package com.nextwist.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import javax.mail.Message;
import javax.mail.MessagingException;
import javax.mail.PasswordAuthentication;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;

import org.codehaus.jettison.json.JSONObject;

import com.nexttwist.common.Constants;
import com.nexttwist.common.DBContants;
import com.nexttwist.common.Type;

public class CommonDAO extends BaseDAO{
	
	
	/**
	 * This method is used to add notification
	 * @param userId
	 * @param storyId
	 * @param storylineId
	 * @param entrylineId
	 * @param event
	 */
	public void  addtonotification(int userId, int storyId, int storylineId, int entrylineId, int event, Type type) {        
	   try{
		        PreparedStatement query = conn.prepareStatement(DBContants.GET_FOLLOWERS);
		        query.setInt(1, userId);
		        ResultSet result = query.executeQuery();
		        long time = Math.round(System.currentTimeMillis()/1000);
		        if(event != 4) {
		            int uid = 0;
		            if( event <2) {
						PreparedStatement query1 = conn.prepareStatement(DBContants.GET_ENTRYLINE_USER);
						query1.setInt(1, entrylineId);
						ResultSet result1 = query1.executeQuery();
						while(result1.next()) {
							PreparedStatement querys1 = conn.prepareStatement(DBContants.GET_FOLLOWERS);
							querys1.setInt(1, result1.getInt("crtdbyid"));
							ResultSet results1 = querys1.executeQuery();
							while(results1.next()) {
								PreparedStatement updatePrepare = conn.prepareStatement(DBContants.DELETE_NOTIFICATION);
								updatePrepare.setInt(1, results1.getInt("followerid"));
								updatePrepare.setInt(2, result1.getInt("crtdbyid"));
								updatePrepare.setInt(3, storyId);
								updatePrepare.setInt(4, storylineId);
								updatePrepare.setInt(5, entrylineId);
								updatePrepare.executeUpdate();
								
								PreparedStatement insertPrepare = conn.prepareStatement(DBContants.ADD_NOTIFICATION);
								insertPrepare.setInt(1, results1.getInt("followerid"));
								insertPrepare.setInt(2, result1.getInt("crtdbyid"));
								insertPrepare.setInt(3, storyId);
								insertPrepare.setInt(4, storylineId);
								insertPrepare.setInt(5, entrylineId);
								insertPrepare.setInt(6, event);
								insertPrepare.setString(7, String.valueOf(time));
								insertPrepare.setString(8, type.toString());
								insertPrepare.executeUpdate();
							}
							PreparedStatement insertPrepare1 = conn.prepareStatement(DBContants.ADD_NOTIFICATION);
							insertPrepare1.setInt(1, result1.getInt("crtdbyid"));
							insertPrepare1.setInt(2, userId);
							insertPrepare1.setInt(3, storyId);
							insertPrepare1.setInt(4, storylineId);
							insertPrepare1.setInt(5, entrylineId);
							insertPrepare1.setInt(6, event);
							insertPrepare1.setString(7, String.valueOf(time));
							insertPrepare1.setString(8, type.toString());
							insertPrepare1.executeUpdate();
							uid = result1.getInt("crtdbyid");
						}
		            }
		            while(result.next()) {
		                if(uid != result.getInt("followerid")) {
		                	PreparedStatement updatePreparestatement = conn.prepareStatement(DBContants.DELETE_NOTIFICATION);
		                	updatePreparestatement.setInt(1, result.getInt("followerid"));
		                	updatePreparestatement.setInt(2, userId);
		                	updatePreparestatement.setInt(3, storyId);
		                	updatePreparestatement.setInt(4, storylineId);
		                	updatePreparestatement.setInt(5, entrylineId);
							updatePreparestatement.executeUpdate();
							
							PreparedStatement insertPreparestatement = conn.prepareStatement(DBContants.ADD_NOTIFICATION);
							insertPreparestatement.setInt(1, result.getInt("followerid"));
							insertPreparestatement.setInt(2, userId);
							insertPreparestatement.setInt(3, storyId);
							insertPreparestatement.setInt(4, storylineId);
							insertPreparestatement.setInt(5, entrylineId);
							insertPreparestatement.setInt(6, event);
							insertPreparestatement.setString(7, String.valueOf(time));
							insertPreparestatement.setString(8, type.toString());
							insertPreparestatement.executeUpdate();
							
		                }
		            }
		        } else {
		        	PreparedStatement queryprepare = conn.prepareStatement(DBContants.GET_ENTRYLINE_USER);
		        	queryprepare.setInt(1, entrylineId);
		        	ResultSet resultprepare = query.executeQuery();

		            while(resultprepare.next()) {
		            	PreparedStatement querys = conn.prepareStatement(DBContants.GET_FOLLOWERS);
		            	querys.setInt(1, resultprepare.getInt("crtdbyid"));
		                ResultSet results = querys.executeQuery();
		                while(results.next()) {
		                	PreparedStatement insertPreparestatement = conn.prepareStatement(DBContants.ADD_NOTIFICATION);
							insertPreparestatement.setInt(1, result.getInt("followerid"));
							insertPreparestatement.setInt(2, result.getInt("crtdbyid"));
							insertPreparestatement.setInt(3, storyId);
							insertPreparestatement.setInt(4, storylineId);
							insertPreparestatement.setInt(5, entrylineId);
							insertPreparestatement.setInt(6, 5);
							insertPreparestatement.setString(7, String.valueOf(time));
							insertPreparestatement.setString(8, type.toString());
		                	insertPreparestatement.executeUpdate();
		                }
		            	PreparedStatement insertPrepare = conn.prepareStatement(DBContants.ADD_NOTIFICATION);
						insertPrepare.setInt(1, result.getInt("crtdbyid"));
						insertPrepare.setInt(2, userId);
						insertPrepare.setInt(3, storyId);
						insertPrepare.setInt(4, storylineId);
						insertPrepare.setInt(5, entrylineId);
						insertPrepare.setInt(6, event);
						insertPrepare.setString(7, String.valueOf(time));
						insertPrepare.setString(8, type.toString());
						insertPrepare.executeUpdate();
		            }    
		        }
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
	}
	
	
	/**
	 * This method is used to checking whether the email is already registered or not
	 * @param email
	 * @return true/false
	 */
	public boolean isEmailAvailable(String email) {
		try{
			PreparedStatement checkPrepare = conn.prepareStatement(DBContants.GET_USER_DETAILS_BYEMAIL);
			checkPrepare.setString(1, email);
			ResultSet result = checkPrepare.executeQuery();
			result.last();
			if (result.getRow() == 1) {
				return false;
			} else {
				return true;
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return false;
		}
	}
	
	/**
	 * This method is used to get the user details
	 * @param userId
	 * @return JSONObject
	 */
	public JSONObject getUserDetails(int userId){
		try{
			PreparedStatement getPrepare = conn.prepareStatement(DBContants.GET_USER_DETAILS_BYID);
			getPrepare.setInt(1, userId);
			ResultSet result = getPrepare.executeQuery();
			JSONObject jsonObject = new JSONObject();
				if(result != null){
					while(result.next()){
						jsonObject.put("userName", result.getString("username"));
						jsonObject.put("email", result.getString("email"));
						//TO DO: if want more columns then need to add
					}
					return jsonObject;
				} else {
					return null;
				}
				
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return null;
		}
	}
	
	
	/**
	 * This method is used to get the story line details
	 * @param storylineId
	 * @param storyId
	 * @return JSONObject
	 */
	public JSONObject getStorylineDetails(int storylineId, int storyId){
		try{
			PreparedStatement getPrepare = conn.prepareStatement(DBContants.GET_STORYLINE_DETAILS);
			getPrepare.setInt(1, storylineId);
			getPrepare.setInt(2, storyId);
			ResultSet result = getPrepare.executeQuery();
			JSONObject jsonObj = new JSONObject();
			while(result.next()) {
				jsonObj.put("createdTime", result.getString("crtdtime"));
				//TODO: if we need more info about the story line we need to ADD here
			}
			return jsonObj;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return null;
		}
	}
	
	/**
	 * This method is used to identify if a notification is already read or not
	 * @param uniqueNotificationId
	 * @return true/false
	 */
	public boolean readnotification(int uniqueNotificationId) {
		try{
			PreparedStatement readPreparestatement = conn.prepareStatement("UPDATE notification SET readflag = 'Y' WHERE vote = ? and delflag = 'N'");
			readPreparestatement.setInt(1, uniqueNotificationId);
			int readCheck = readPreparestatement.executeUpdate();
			if(readCheck > 0){
				return true;
			} else{
				return false;
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return false;
		}
	}
	
	/**
	 * This method is used to check whether a username is available or not
	 * @param userName
	 * @return true/false
	 */
	public boolean checkavailable(String userName)	{
		try{
			Boolean isPresent = true;
			PreparedStatement checkPrepare = conn.prepareStatement(DBContants.GET_USER_DETAILS_BYNAME);
			checkPrepare.setString(1, userName);
			ResultSet result = checkPrepare.executeQuery();
			while(result.next()) {
				isPresent = false; 
			}
			return isPresent;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return false;
		}
	}
	
	/**
	 * Thsi method is used to subscribe a user to notification 
	 * @param userId
	 * @return true/false
	 */
	public boolean subscribe(int userId){
		try{
			PreparedStatement updatePrepare = conn.prepareStatement("update users set sendnotify = 'Y' where userid = ? AND `delete flag` = 'N'");
			updatePrepare.setInt(1, userId);
			int updateCheck = updatePrepare.executeUpdate();
			if(updateCheck > 0){ 
				return true; 
			} else {
				return false;
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return false;
		}
	}
	
	/**
	 * This method is used to Unsubscribe a user from notification 
	 * @param userId
	 * @return true/false
	 */
	public boolean unsubscribe(int userId){
		try{
			PreparedStatement updatePrepare = conn.prepareStatement("update users set sendnotify = 'N' where userid = ? AND `delete flag` = 'N'");
			updatePrepare.setInt(1, userId);
			int updateCheck = updatePrepare.executeUpdate();
			if(updateCheck > 0){ 
				return true; 
			} else {
				return false;
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return false;
		}
	}
	
	/**
	 * This method is used to check the user is subscribed to notification
	 * @param userId
	 * @return true/false
	 */
	public boolean issubscribe(int userId){
		try{
			PreparedStatement getPrepare = conn.prepareStatement("select * from users where userid = ? AND `delete flag` = 'N' AND sendnotify = 'Y'");
			getPrepare.setInt(1, userId);
			ResultSet result = getPrepare.executeQuery();
			result.last();
			if(result.getRow() > 0){
				return true;
			} else {
				return false;
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return false;
		}
	}
	
	/**
	 * This method is used to get the new user id
	 * @return user id
	 * @throws Exception 
	 */
	public int getNewUserId() throws Exception{
		try{
			int userId = 1;
			PreparedStatement getPrepare = conn.prepareStatement(DBContants.MAX_USER_ID);
			ResultSet result = getPrepare.executeQuery();
			while(result.next()) {
				userId = result.getInt("uid");
				userId++;
			}
			return userId;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			throw e;
		}
	}
	
	/**
	 * This method is used to get the new story id
	 * @return story id
	 * @throws Exception 
	 */
	public int getNewStoryId() throws Exception{
		try{
			int storyId = 1;
			PreparedStatement getPrepare = conn.prepareStatement(DBContants.MAX_STORY_ID);
			ResultSet result = getPrepare.executeQuery();
			while(result.next()) {
				storyId = result.getInt("sid");
				storyId++;
			}
			return storyId;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			throw e;
		}
	}
	
	/**
	 * This method is used to get the new storyline id
	 * @param storyId
	 * @return story line id
	 * @throws Exception 
	 */
	public int getNewStoryLineid(int storyId) throws Exception{
		try{
			int storyLineId = 1;
			PreparedStatement getPrepare = conn.prepareStatement(DBContants.MAX_STORYLINE_ID);
			getPrepare.setInt(1, storyId);
			ResultSet result = getPrepare.executeQuery();
			while(result.next()) {
				storyLineId = result.getInt("sid");
				storyLineId++;
			}
			return storyLineId;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			throw e;
		}
	}
	
	/**
	 * This method is used to get the new entryline for a story
	 * @param storyId
	 * @return entryline number
	 * @throws Exception 
	 */
	public int getNewEntryid(int storyId) throws Exception{
		try{
			int entryLineId = 1;
			PreparedStatement getPrepare = conn.prepareStatement(DBContants.MAX_ENTRYLINE_ID);
			getPrepare.setInt(1, storyId);
			getPrepare.setInt(2, getNewStoryLineid(storyId));
			ResultSet result = getPrepare.executeQuery();
			while(result.next()) {
				entryLineId = result.getInt("eid");
				entryLineId++;
			}
			return entryLineId;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			throw e;
		}
	}
	
	/**
	 * This method is used to get the no of lines that user wrote and accepted as the storyline
	 * @param userId
	 * @return no of lines
	 * @throws Exception 
	 */
	public int numberofStoryLine(int userId) throws Exception{
		try{
			PreparedStatement getPrepare = conn.prepareStatement(DBContants.STORYLINE_COUNT);
			getPrepare.setInt(1, userId);
	        ResultSet rs = getPrepare.executeQuery();
	        rs.last();
	        return rs.getRow();
		} catch (Exception e) {
			System.out.println(e.getMessage());
			throw e;
		}
    }
	
	/**
	 * This method is used to get the user level
	 * @param userId
	 * @return String
	 * @throws Exception 
	 */
	public String userLabel(int userId) throws Exception {
		int countLine = numberofStoryLine(userId);
		if(countLine > Constants.EXPERT) {
			return Constants.SUPEREXPERTTITLE;
		} else if(countLine > Constants.SPWRITER) {
			return Constants.EXPERTTITLE;
		} else if(countLine > Constants.NEWBIE) {
			return Constants.SPWRITERTITLE;
		} else {
			return Constants.NEWBIETITLE;
		}
	}
	
	
	/**
	 * This method is used get the user's image path
	 * @param userId
	 * @param type
	 * @return image path
	 */
	//TODO: QR is still left
	public String userImage(int userId, String type) {
		try{
			PreparedStatement getPrepare = conn.prepareStatement("SELECT `profile image path` from users WHERE userid = ? and `delete flag` = 'N'");
			getPrepare.setInt(1, userId);
			ResultSet rs = getPrepare.executeQuery();
			String imagePath = "";
			String qrCode = "QRcode"; //TODO: need to generate or check the QR code
			//Generating QR code for the user
			/*$qr = new qrcode();
			$qr->link("http://nexttwist.tigmaminds.com/newui/?page=profile&id=" . $userid);			//link qr code
			$imgSrc = $qr->get_link();*/	
			while(rs.next()){
				if(type == "small"){
					imagePath = (rs.getString("profile image path").equals("users/defaultAvatar/")) ? qrCode : rs.getString("profile image path")+"/small.jpg"; 
				} else if(type == "large"){
					imagePath = (rs.getString("profile image path").equals("users/defaultAvatar/")) ? qrCode : rs.getString("profile image path")+"/dp.jpg";
				}
			}
			return imagePath;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return null;
		}
	}
	
	/**
	 * This method is used to get the timeAgo
	 * @param time
	 * @return String
	 */
	public String timeAgo(String time){
		//Date date = new Date(time);
		long givenDate = Math.round(Long.parseLong(time) / 1000);
		long currentTime = Math.round(System.currentTimeMillis() / 1000);
		
		long timeDiff = currentTime-givenDate;
		Double show;
		String finalString;
		if(timeDiff > 31104000){ // code for yrs
			show = Math.floor(timeDiff/31104000);
			if(show > 1){
				finalString = "about " + show + " years ago";
			} else{
				finalString = "about " + show + " year ago";
			}
		} else if(timeDiff > 2592000){ // code ofr months
			show = Math.floor(timeDiff/2592000);
			if(show > 1){
				finalString = "about " + show + " months ago";
			} else{
				finalString = "about " + show + " month ago";
			}
		} else if(timeDiff > 86400) {  // code for days
			show = Math.floor(timeDiff/86400);
			if(show > 1) {
				finalString = "about " + show + " days ago";
			} else{
				finalString = "about " + show + " day ago";
			}
		} else if(timeDiff > 3600){ // coed for hrs
			show = Math.floor(timeDiff/(3600));
			if(show > 1) {
				finalString = "about " + show + " hours ago";
			} else{
				finalString = "about " + show + " hour ago";
			}
		} else if(timeDiff > 60) { // code for months
			show = Math.floor(timeDiff/(60));
			if(show > 1) {
				finalString = "about " + show + " minutes ago";
			} else{
				finalString = "about " + show + " minute ago";
			}
		} else {
			finalString = "about few seconds ago";
		}
		return finalString;
	}
	
	
	/**
	 * This method is used to get the entry line time
	 * @param uniqueId
	 * @return time
	 */
	public String getEntryLineTime(int uniqueId){
		try{
			PreparedStatement getPrepare = conn.prepareStatement(DBContants.SELECT_ENTRYLINE_TIME);
			getPrepare.setInt(1, uniqueId);
			String time = "";
			ResultSet rs = getPrepare.executeQuery();
			while(rs.next()){
				time = rs.getString("crtdtime");
			}
			return time;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return null;
		}
	}

	/**
	 * This method is used to get the story line time
	 * @param uniqueId
	 * @return time
	 * @throws Exception 
	 */
	 public String getStoryLineTime(int uniqueId) throws Exception{
		try{
			PreparedStatement getPrepare = conn.prepareStatement(DBContants.SELECT_STORY_TIME);
			getPrepare.setInt(1, uniqueId);
			String time = "";
			ResultSet rs = getPrepare.executeQuery();
			while(rs.next()){
				time = rs.getString("crtdtime");
			}
			return time;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			throw e;
		}
	}
	
	 /**
	  * This method is used to get the no of like in a line
	  * @param uniqueId
	  * @return no of like
	 * @throws Exception 
	  */
	 public int getVoteUpCount(int uniqueId) throws Exception{
		 try{
			 PreparedStatement getPrepare = conn.prepareStatement(DBContants.SELECT_VOTE_COUNT_ENTRYLINE);
			 getPrepare.setInt(1, uniqueId);
			 getPrepare.setInt(2, 1);
			 ResultSet rs = getPrepare.executeQuery();
			 rs.last();
			 int count = 0;
			 count = rs.getRow();
			 return count;
		 } catch (Exception e) {
			 System.out.println(e.getMessage());
			 throw e;
		 }
	 }
	 
	 /**
	  * This method is used to get the no of dislike in a line
	  * @param uniqueId
	  * @return no of dislike
	 * @throws Exception 
	  */
	 public int getVoteDownCount(int uniqueId) throws Exception{
		 try{
			 PreparedStatement getPrepare = conn.prepareStatement(DBContants.SELECT_VOTE_COUNT_ENTRYLINE);
			 getPrepare.setInt(1, uniqueId);
			 getPrepare.setInt(2, -1);
			 ResultSet rs = getPrepare.executeQuery();
			 rs.last();
			 int count = 0;
			 count = rs.getRow();
			 return count;
		 } catch (Exception e) {
			 System.out.println(e.getMessage());
			 throw e;
		 }
	 }
	 
	 /**
	  * This method is used to send the mails
	  */
	 public void sendMail(){
		 Properties props = new Properties();
			props.put("mail.smtp.host", "smtp.gmail.com");
			props.put("mail.smtp.socketFactory.port", "465");
			props.put("mail.smtp.socketFactory.class",
					"javax.net.ssl.SSLSocketFactory");
			props.put("mail.smtp.auth", "true");
			props.put("mail.smtp.port", "465");
	 
			Session session = Session.getDefaultInstance(props,
				new javax.mail.Authenticator() {
					protected PasswordAuthentication getPasswordAuthentication() {
						return new PasswordAuthentication("sandipan013@gmail.com","");
					}
				});
	 
			try {
	 
				Message message = new MimeMessage(session);
				message.setFrom(new InternetAddress("sandipan013@gmail.com"));
				message.setRecipients(Message.RecipientType.TO,
						InternetAddress.parse("dip06cs03@gmail.com"));
				message.setSubject("Testing Subject");
				message.setText("Dear Mail Crawler," +
						"\n\n No spam to my email, please!");
	 
				Transport.send(message);
	 
				System.out.println("Done");
	 
			} catch (MessagingException e) {
				throw new RuntimeException(e);
			}
	 }
	 
	 /**
	  * To check whether the user exists or not
	  * 
	  * @param userId
	  * @return
	 * @throws SQLException 
	  */
	 
	public boolean isUserExists(int userId) throws SQLException {
		boolean isUserExists = false;
		try {

			PreparedStatement checkFollower = conn
					.prepareStatement(DBContants.GET_USER_BY_USERID);
			checkFollower.setInt(1, userId);
			ResultSet followerRs = checkFollower.executeQuery();
			if (followerRs.next()) {
				do {
					isUserExists = true;
				} while (followerRs.next());
			} else {
				isUserExists = false;
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new SQLException(e);
		}

		return isUserExists;
	}
	 
}
